How do you create a calendar drop down in excel?

Does anyone know how you would create a calendar drop down box in excel?

What do You mean exactly by a "Calander 'drop down box'". If you want to have
a calander pop up when a user clicks in a certain cell, you can create a
userform with a calander control on it. The calander control is an add-on
control however and will only exist on systems that have it installed. If
it's not installed, the function will error out. To have it pop up for
clicking certain cells, add an event to the selection change in the worksheet
module.
--
When you lose your mind, you free your life.


"Calendar drop down box in excel" wrote:

> Does anyone know how you would create a calendar drop down box in excel?

As Ben mentioned you have to watch this one, I spent awhile making a
Calendar (amazingly simple) and it worked fine. However the Calednar
control is usually installed with MS Office Pro and not the Standard version
we use at work. You can download the necessary file but all users must have
the Active X file installed on their machine, my place of work will not
allow that. If you can use the Calendar then note there are different
versions for Office 97, 2002 and 2002 ( I think)

Useful links include -

http://www.ozgrid.com/VBA/excel-calendar-dates.htm

http://www.ozgrid.com/News/excel-calendar-dates.htm

http://www.ozgrid.com/News/excel-calendar-dates.htm

Good luck,
Mickey

If you are interested, I have several VBA functions that work in the
following way:

1. User clicks a button on a sheet or toolbar.
2. A formless calendar appears on the sheet.
3. The user selects a date on the calendar.
4. The user clicks the button again (#1 above).
5. The program captures the date selected and deletes the calendar.

No user form is needed. It's very clean. But, as mentioned in previous
posts, you must have the control to begin with and so must all your users.

If you are interested please post back and I'll post the functions needed.

"Calendar drop down box in excel" wrote:

> Does anyone know how you would create a calendar drop down box in excel?

I would be interested in your way of creating this. What are the functions
that you use?

"quartz" wrote:

> If you are interested, I have several VBA functions that work in the
> following way:
>
> 1. User clicks a button on a sheet or toolbar.
> 2. A formless calendar appears on the sheet.
> 3. The user selects a date on the calendar.
> 4. The user clicks the button again (#1 above).
> 5. The program captures the date selected and deletes the calendar.
>
> No user form is needed. It's very clean. But, as mentioned in previous
> posts, you must have the control to begin with and so must all your users.
>
> If you are interested please post back and I'll post the functions needed.
>
> "Calendar drop down box in excel" wrote:
>
> > Does anyone know how you would create a calendar drop down box in excel?

I have some examples on this page if you want to see it
http://www.rondebruin.nl/calendar.htm



--
Regards Ron de Bruin
http://www.rondebruin.nl



"Create a calendar drop down box" <Createacalendardropdownbox@discussions.microsoft.com> wrote in message
news:D6CE2183-A75D-4A17-8A83-F0EC4C1A2995@microsoft.com...
>I would be interested in your way of creating this. What are the functions
> that you use?
>
> "quartz" wrote:
>
>> If you are interested, I have several VBA functions that work in the
>> following way:
>>
>> 1. User clicks a button on a sheet or toolbar.
>> 2. A formless calendar appears on the sheet.
>> 3. The user selects a date on the calendar.
>> 4. The user clicks the button again (#1 above).
>> 5. The program captures the date selected and deletes the calendar.
>>
>> No user form is needed. It's very clean. But, as mentioned in previous
>> posts, you must have the control to begin with and so must all your users.
>>
>> If you are interested please post back and I'll post the functions needed.
>>
>> "Calendar drop down box in excel" wrote:
>>
>> > Does anyone know how you would create a calendar drop down box in excel?

Five functions are involved. I usually put the following in a separate
module. Assign your button to call Calendar_Main() to run it. As previously
stated, you click one time to pop the calendar up, select a date, then click
the same button again to capture the date and delete the calendar object.

I think mine runs a little differently from Ron's. The way this is set now,
it will popup a message box displaying the date selected. That is where you
would pass the date back to your calling sub. Just place all five of the
following into one standard code module. I hope you like it.

Private Sub Calendar_Main()
'MAIN CALLING PROGRAM: ADDS/RETRIEVES/DELETES CALENDAR CONTROL;
'THE FUNCTION IS RUN TWICE: ONCE TO LOAD THE CONTROL AND ONCE
'TO CAPTURE THE VALUE AND UNLOAD THE CONTROL; ON THE FIRST CALL
'NO VALUE HAS BEEN ASSIGNED YET (EXIT SUB);
Dim dteCalendarValue As Date
dteCalendarValue = CalendarPopupProgram
If UCase(dteCalendarValue) = "12:00:00 AM" Then Exit Sub
'Optionally run other procedures here - pass the date back to other routines
'instead of just displaying the date captured (as in the following line);
MsgBox Format(dteCalendarValue, "MM/DD/YYYY")
End Sub

Private Function CalendarPopupProgram() As Date
'CREATE/DELETE CALENDAR ACTIVEX CONTROL
Dim strCalendarName As String
Dim dteCalendarValue As Date
'If calendar exists: obtain the selected date and delete the calendar object
strCalendarName = CalendarGetName
If strCalendarName <> "" Then
dteCalendarValue = ActiveSheet.OLEObjects(strCalendarName).Object.Value
ActiveSheet.Shapes(strCalendarName).Delete
CalendarPopupProgram = DateSerial(Year(dteCalendarValue),
Month(dteCalendarValue), Day(dteCalendarValue))
End If
'If calendar does not exist: create it
If strCalendarName = "" Then Call CalendarAdd
End Function

Private Function CalendarAdd()
'ADD A FORMLESS ACTIVEX CALENDAR CONTROL TO THE ACTIVE SHEET; EXIT
'DESIGN MODE FOR USER INTERFACE, CENTER THE OBJECT ON SCREEN; SET
'CALENDAR VALUE EQUAL TO TODAY'S DATE;
Application.ScreenUpdating = False
Dim objCalendar As OLEObject
Dim objWorkSheet As Worksheet
Dim varCenter() As Variant
Set objWorkSheet = Parent.ActiveSheet
Set objCalendar = objWorkSheet.OLEObjects.Add(ClassType:="MSCAL.Calendar",
Link:=False, DisplayAsIcon:=False)
varCenter = ScreenCenterCompact
objCalendar.Top = varCenter(1) - 72 'Adjust center of screen for 1/2
height of object to center
objCalendar.Left = varCenter(2) - 108 'Adjust center of screen for 1/2
width of object to center
objCalendar.Border.Weight = 3#
objCalendar.Border.ColorIndex = 9 '1, 9, 23, 25
objCalendar.Object.Value = Now()
objCalendar.Visible = True
objCalendar.Visible = False
Application.ScreenUpdating = True
objCalendar.Visible = True
Set objWorkSheet = Nothing
Set objCalendar = Nothing
End Function

Private Function CalendarGetName() As String
'RETURN THE NAME OF THE CALENDAR OBJECT ON THE ACTIVE
'SHEET IF ONE EXISTS; OTHERWISE RETURN EMPTY STRING;
Dim lngCount As Long
Dim lngX As Long
lngCount = ActiveSheet.Shapes.Count
If Not lngCount > 0 Then CalendarGetName = "": Exit Function
For lngX = 1 To lngCount
If UCase(Left(ActiveSheet.Shapes(lngX).Name, 8)) = "CALENDAR" Then
CalendarGetName = ActiveSheet.Shapes(lngX).Name: Exit Function
Next lngX
End Function

Private Function ScreenCenterCompact() As Variant
'CALCULATE CENTER OF VISIBLE SCREEN;
Dim strVisible As String
Dim varCoordinates(2) As Variant
strVisible = Windows(1).VisibleRange.Address
varCoordinates(1) = (Range(Windows(1).VisibleRange.Cells(1, 1).Address).Top
+ (Range(Range(Windows(1).VisibleRange.Cells(1,
1).Address).Offset(Range(strVisible).Rows.Count - 1,
Range(strVisible).Columns.Count - 1).Address).Top -
Range(Windows(1).VisibleRange.Cells(1, 1).Address).Top) / 2)
varCoordinates(2) = (Range(Windows(1).VisibleRange.Cells(1, 1).Address).Left
+ (Range(Range(Windows(1).VisibleRange.Cells(1,
1).Address).Offset(Range(strVisible).Rows.Count - 1,
Range(strVisible).Columns.Count - 1).Address).Left -
Range(Windows(1).VisibleRange.Cells(1, 1).Address).Left) / 2)
ScreenCenterCompact = varCoordinates
End Function

HTH

"Create a calendar drop down box" wrote:

> I would be interested in your way of creating this. What are the functions
> that you use?
>
> "quartz" wrote:
>
> > If you are interested, I have several VBA functions that work in the
> > following way:
> >
> > 1. User clicks a button on a sheet or toolbar.
> > 2. A formless calendar appears on the sheet.
> > 3. The user selects a date on the calendar.
> > 4. The user clicks the button again (#1 above).
> > 5. The program captures the date selected and deletes the calendar.
> >
> > No user form is needed. It's very clean. But, as mentioned in previous
> > posts, you must have the control to begin with and so must all your users.
> >
> > If you are interested please post back and I'll post the functions needed.
> >
> > "Calendar drop down box in excel" wrote:
> >
> > > Does anyone know how you would create a calendar drop down box in excel?

Wow, that link isn't dominated with advertisements like that Ozgrid link.

In fact, there are no advertisements.

--
Regards,
Tom Ogilvy


"Ron de Bruin" <rondebruin@kabelfoon.nl> wrote in message
news:%23Y4DZ9ATFHA.3012@TK2MSFTNGP14.phx.gbl...
> I have some examples on this page if you want to see it
> http://www.rondebruin.nl/calendar.htm
>
>
>
> --
> Regards Ron de Bruin
> http://www.rondebruin.nl
>
>
>
> "Create a calendar drop down box"
<Createacalendardropdownbox@discussions.microsoft.com> wrote in message
> news:D6CE2183-A75D-4A17-8A83-F0EC4C1A2995@microsoft.com...
> >I would be interested in your way of creating this. What are the
functions
> > that you use?
> >
> > "quartz" wrote:
> >
> >> If you are interested, I have several VBA functions that work in the
> >> following way:
> >>
> >> 1. User clicks a button on a sheet or toolbar.
> >> 2. A formless calendar appears on the sheet.
> >> 3. The user selects a date on the calendar.
> >> 4. The user clicks the button again (#1 above).
> >> 5. The program captures the date selected and deletes the calendar.
> >>
> >> No user form is needed. It's very clean. But, as mentioned in previous
> >> posts, you must have the control to begin with and so must all your
users.
> >>
> >> If you are interested please post back and I'll post the functions
needed.
> >>
> >> "Calendar drop down box in excel" wrote:
> >>
> >> > Does anyone know how you would create a calendar drop down box in
excel?
>
>

That worked perfectly!!! Thank you so much. D

"Ron de Bruin" wrote:

> I have some examples on this page if you want to see it
> http://www.rondebruin.nl/calendar.htm
>
>
>
> --
> Regards Ron de Bruin
> http://www.rondebruin.nl
>
>
>
> "Create a calendar drop down box" <Createacalendardropdownbox@discussions.microsoft.com> wrote in message
> news:D6CE2183-A75D-4A17-8A83-F0EC4C1A2995@microsoft.com...
> >I would be interested in your way of creating this. What are the functions
> > that you use?
> >
> > "quartz" wrote:
> >
> >> If you are interested, I have several VBA functions that work in the
> >> following way:
> >>
> >> 1. User clicks a button on a sheet or toolbar.
> >> 2. A formless calendar appears on the sheet.
> >> 3. The user selects a date on the calendar.
> >> 4. The user clicks the button again (#1 above).
> >> 5. The program captures the date selected and deletes the calendar.
> >>
> >> No user form is needed. It's very clean. But, as mentioned in previous
> >> posts, you must have the control to begin with and so must all your users.
> >>
> >> If you are interested please post back and I'll post the functions needed.
> >>
> >> "Calendar drop down box in excel" wrote:
> >>
> >> > Does anyone know how you would create a calendar drop down box in excel?
>
>
>

I have a rolling task list for days events that must be accomplished company wide. I would like a way for my excel list to drop off these items listed in a column by date. Basically I want at midnight tonight 3/22/10 to drop out of excel and be replaced by the next days task, so now 3/23/10 would be my first column.